package com.trkj.dao;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import com.trkj.pojo.Emp;

public interface EmpDAO extends JpaRepository<Emp, Integer>,JpaSpecificationExecutor<Emp>{
	@Query("select e from Emp e inner join e.position p where p.positionId = ?1")
	public Emp findOneByPosition(Integer positionid);


	/**
	 * 登录
	 * @param name
	 * @param pass
	 * @return
	 */
    @Query("select e from Emp e where e.empLoginname=?1 and e.empPassword=?2")
    public Emp queryByNameAndPassword(String name, String pass);

    /**
     * 查询个人员工信息
     * @param empid
     * @return 
     */
  /*  @Query(nativeQuery=true,value="select e.emp_name,e.emp_loginname,e.emp_phone,e.emp_cornet,\r\n" + 
    		"e.emp_sex,e.emp_birthday,e.emp_email,e.emp_address,e.emp_QQ,\r\n" + 
    		"m.role_name,d.dept_name,p.position_name,e.emp_id\r\n" + 
    		"from emp e,menurole m,dept d,position p,`roe-user` r\r\n" + 
    		"where e.dept_id=d.dept_id and e.position_id=p.position_id\r\n" + 
    		"and e.emp_id=r.emp_id and m.role_id=r.role_id and e.emp_id=?")
    public EmpAndDeptVo queryEmpandMenuroleandDeptandRoeuser(Integer empid);
    */
    
    
    
    
//    @Query("select p.perId from Emp e inner join e.menuroles m inner join m.permissions p where e.empId=?1")
//    public List<Integer> queryPerIdsByEmpId(Integer empid);

//  @Query("select f.functionId from User u inner join u.roles r inner join r.permissions f where u.id=?1")
	/**
	 * 查询员工角色
	 * wsf
	 * @return
	 */
	@Query(nativeQuery = true, value = "SELECT (SELECT d.dept_name from dept d where d.dept_id=e.dept_id) deptname,e.emp_id,e.emp_sex,e.emp_name,(SELECT p.position_name from position p where p.position_id=e.position_id) positionname, (SELECT m.role_name from menurole m WHERE m.role_id=ru.role_id) rolename from emp e INNER JOIN `roe-user` ru on e.emp_id = ru.emp_id")
	public List<Object[]> findEmpRole();
	/**
	 * 根据角色id和部门id查询员工是否拥有该角色
	 * wsf
	 */
	@Query(nativeQuery = true, value = "SELECT e.emp_id, e.emp_name ,(SELECT 1 FROM `roe-user` r where r.emp_id = e.emp_id AND r.role_id=?1) v FROM emp e WHERE e.dept_id = ?2")
	public  List<Object[]> findEmpRoleByEmpIdAndDeotId(Integer roleid, Integer deptid);
	
	
	public List<Emp> findEmpByContractsIsNull();
	
	public List<Emp> findEmpByContractsIsNullAndEmpNameLike(String empName);
	
	public List<Emp> findEmpByContractsIsNullAndDeptDeptNameLike(String deptName);
	
	public List<Emp> findEmpByContractsIsNullAndDeptDeptNameLikeAndEmpNameLike(String deptName,String empName);
	
	public List<Emp> findEmpByContractsIsNotNull();
	
	public List<Emp> findEmpByContractsIsNotNullAndEmpNameLike(String empName);
	
	public List<Emp> findEmpByContractsIsNotNullAndDeptDeptNameLike(String deptName);
	
	public List<Emp> findEmpByContractsIsNotNullAndDeptDeptNameLikeAndEmpNameLike(String deptName,String empName);
	
	@Query(nativeQuery=true,value="select emp.emp_id,emp.emp_name,dept.dept_name,emp.emp_loanbalance emp from emp\r\n" + 
			" INNER JOIN dept on emp.dept_id=dept.dept_id ")
	public List<Object[]> findemploanInfo();
	@Query("select e from Emp e where e.empEmail=?1")
	public Emp selectempbyemail(String empemail);
	/*不要删*/
	//public List<Emp> findEmpByContractsIsNullAndDeptDeptNameLikeAndEmpNameLike(String deptName, String empName);
	@Query(nativeQuery=true,value="select emp.emp_id,emp.emp_name,dept.dept_name,emp.emp_loanbalance emp from emp\r\n" + 
			" INNER JOIN dept on emp.dept_id=dept.dept_id \r\n" + 
			" where emp.emp_name like ?1 \r\n" + 
			" OR dept.dept_name LIKE ?2")
	public List<Object[]> findemploanORtiaojian(String empName,String deptName);
	
	@Query(nativeQuery=true,value="select emp.emp_id,emp.emp_name,dept.dept_name,emp.emp_loanbalance emp from emp\r\n" + 
			" INNER JOIN dept on emp.dept_id=dept.dept_id \r\n" + 
			" where emp.emp_name like ?1 \r\n" + 
			" AND dept.dept_name LIKE ?2")
	public List<Object[]> findemploanANDtiaojian(String empName,String deptName);
	

	@Query("select e from Emp e where e.empName=?1")
	public Emp selectempbyname(String name);
}
